﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using stwh_Common.DBUtility;//Please add references
namespace stwh_DAL
{
	/// <summary>
	/// 数据访问类:stwh_order
	/// </summary>
    public partial class stwh_orderDAL : BaseDAL
	{
		public stwh_orderDAL()
		{}
		#region  BasicMethod
        /// <summary>
        /// 分页获取文本消息
        /// </summary>
        /// <param name="FieldColumn">排序的列名</param>
        /// <param name="FieldOrder">降序排列还是升序排列</param>
        /// <param name="If">查询条件</param>
        /// <param name="pageSize">每页显示的条数</param>
        /// <param name="pageNumber">页码</param>
        /// <param name="selectCount">查询的总记录条数</param>
        /// <param name="d_peopleCount">总记录条数（不带条件）</param>
        /// <returns></returns>
        public DataSet GetListByPage(string FieldColumn, string FieldOrder, string If, int pageSize, int pageNumber, ref int selectCount, ref int d_peopleCount)
        {
            object obj = DbHelperSQL.GetSingle("select count(1) from view_order");
            d_peopleCount = obj != null ? int.Parse(obj.ToString()) : 0;
            return DbHelperSQL.PageData("view_order", "stwh_orid", FieldColumn, FieldOrder, "stwh_orid,stwh_buid,stwh_orddid,stwh_ortime,stwh_orstatus,stwh_orpaystyle,stwh_oryjstyle,stwh_oryjdd,stwh_orremark,stwh_bumobile,stwh_oraddress,stwh_oruser,stwh_ortel,stwh_oryoubian,stwh_orcity", If, pageSize, pageNumber, ref selectCount);
        }

        /// <summary>
        /// 分页获取文本消息
        /// </summary>
        /// <param name="FieldColumn">排序的列名</param>
        /// <param name="FieldOrder">降序排列还是升序排列</param>
        /// <param name="If">查询条件</param>
        /// <param name="pageSize">每页显示的条数</param>
        /// <param name="pageNumber">页码</param>
        /// <param name="selectCount">查询的总记录条数</param>
        /// <param name="d_peopleCount">总记录条数（带条件）</param>
        /// <returns></returns>
        public DataSet GetListByPage(string FieldColumn, string FieldOrder, string If, int pageSize, int pageNumber, ref int selectCount, ref int d_peopleCount, int flag)
        {
            object obj = DbHelperSQL.GetSingle("select count(1) from view_order where " + If);
            d_peopleCount = obj != null ? int.Parse(obj.ToString()) : 0;
            return DbHelperSQL.PageData("view_order", "stwh_orid", FieldColumn, FieldOrder, "stwh_orid,stwh_buid,stwh_orddid,stwh_ortime,stwh_orstatus,stwh_orpaystyle,stwh_oryjstyle,stwh_oryjdd,stwh_orremark,stwh_bumobile,stwh_oraddress,stwh_oruser,stwh_ortel,stwh_oryoubian,stwh_orcity", If, pageSize, pageNumber, ref selectCount);
        }

		/// <summary>
		/// 得到最大ID
		/// </summary>
		public int GetMaxId()
		{
		    return DbHelperSQL.GetMaxID("stwh_orid", "stwh_order"); 
		}

		/// <summary>
		/// 是否存在该记录
		/// </summary>
		public bool Exists(int stwh_orid)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select count(1) from stwh_order");
			strSql.Append(" where stwh_orid=@stwh_orid");
			SqlParameter[] parameters = {
					new SqlParameter("@stwh_orid", SqlDbType.Int,4)
			};
			parameters[0].Value = stwh_orid;

			return DbHelperSQL.Exists(strSql.ToString(),parameters);
		}

		/// <summary>
		/// 生成订单（更新订单状态）
		/// </summary>
		public int Add(stwh_Model.stwh_order model)
		{
            int result = 0;
            try
            {
                SqlParameter[] parameters = {
                    new SqlParameter("@stwh_buid",SqlDbType.Int,4),
                    new SqlParameter("@stwh_orddid",SqlDbType.NVarChar,100),
                    new SqlParameter("@stwh_orstatus",SqlDbType.Int,4),
                    new SqlParameter("@stwh_orpaystyle",SqlDbType.Int,4),
                    new SqlParameter("@pidAndpcount",SqlDbType.NVarChar,200),
                    new SqlParameter("@result",SqlDbType.Int,4)};
                parameters[0].Value = model.stwh_buid;
                parameters[1].Value = model.stwh_orddid;
                parameters[2].Value = model.stwh_orstatus;
                parameters[3].Value = model.stwh_orpaystyle;
                parameters[4].Value = model.pidAndpcount;
                parameters[5].Direction = ParameterDirection.Output;
                using (DbHelperSQL.RunProcedure("ProcAddOrderDetails", parameters))
                {
                    result = int.Parse(parameters[5].Value.ToString());
                }
            }
            catch (Exception)
            {
            }
            return result;
		}

        /// <summary>
        /// 获取会员最新订单商品总价
        /// </summary>
        /// <param name="stwh_buid">会员id</param>
        /// <returns></returns>
        public decimal GetOrderTotalPrice(int stwh_buid, ref string stwh_orddid)
        {
            decimal result = 0.00m;
            try
            {
                SqlParameter[] parameters = {
                    new SqlParameter("@stwh_buid",SqlDbType.Int,4)};
                parameters[0].Value = stwh_buid;
                using (SqlDataReader reader = DbHelperSQL.RunProcedure("ProcSelectOrderNewUser", parameters))
                {
                    if (reader.Read())
                    {
                        result = decimal.Parse(reader["totalprice"].ToString());
                        stwh_orddid = reader["stwh_orddid"].ToString();
                    }
                }
            }
            catch (Exception)
            {
            }
            return result;
        }

        /// <summary>
        /// 根据订单编号获取会员订单商品总价
        /// </summary>
        /// <param name="stwh_orddid">订单编号</param>
        /// <returns></returns>
        public decimal GetOrderTotalPrice(string stwh_orddid)
        {
            decimal result = 0.00m;
            try
            {
                SqlParameter[] parameters = {
                    new SqlParameter("@stwh_orddid",SqlDbType.NVarChar,100)};
                parameters[0].Value = stwh_orddid;
                using (SqlDataReader reader = DbHelperSQL.RunProcedure("ProcSelectOrderUser", parameters))
                {
                    if (reader.Read()) result = decimal.Parse(reader["totalprice"].ToString());
                }
            }
            catch (Exception)
            {
            }
            return result;
        }

        /// <summary>
        /// 分页获取会员订单信息和订单详细信息
        /// </summary>
        /// <param name="If">查询条件</param>
        /// <param name="pageSize">每页显示多少条</param>
        /// <param name="pageNumber">页码</param>
        /// <returns></returns>
        public DataSet GetOrder(string If, int pageSize, int pageNumber)
        {
            try
            {
                SqlParameter[] parameters = {
                    new SqlParameter("@pageSize",SqlDbType.Int,4),
                    new SqlParameter("@pageNumber",SqlDbType.Int,4),
                    new SqlParameter("@If",SqlDbType.NVarChar,300)};
                parameters[0].Value = pageSize;
                parameters[1].Value = pageNumber;
                parameters[2].Value = If;
                return DbHelperSQL.RunProcedureToDS("ProcSelectOrderdetails", parameters);
                
            }
            catch (Exception)
            {
                return null;
            }
        }

		/// <summary>
		/// 更新一条数据
		/// </summary>
		public bool Update(stwh_Model.stwh_order model)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("update stwh_order set ");
			strSql.Append("stwh_buid=@stwh_buid,");
			strSql.Append("stwh_orddid=@stwh_orddid,");
			strSql.Append("stwh_ortime=@stwh_ortime,");
			strSql.Append("stwh_orstatus=@stwh_orstatus,");
			strSql.Append("stwh_orpaystyle=@stwh_orpaystyle,");
			strSql.Append("stwh_oryjstyle=@stwh_oryjstyle,");
			strSql.Append("stwh_oryjdd=@stwh_oryjdd,");
			strSql.Append("stwh_orremark=@stwh_orremark,");
            strSql.Append("stwh_oraddress=@stwh_oraddress,");
            strSql.Append("stwh_oruser=@stwh_oruser,");
            strSql.Append("stwh_ortel=@stwh_ortel,");
            strSql.Append("stwh_oryoubian=@stwh_oryoubian,");
            strSql.Append("stwh_orcity=@stwh_orcity");
			strSql.Append(" where stwh_orid=@stwh_orid");
			SqlParameter[] parameters = {
					new SqlParameter("@stwh_buid", SqlDbType.Int,4),
					new SqlParameter("@stwh_orddid", SqlDbType.NVarChar,100),
					new SqlParameter("@stwh_ortime", SqlDbType.DateTime),
					new SqlParameter("@stwh_orstatus", SqlDbType.Int,4),
					new SqlParameter("@stwh_orpaystyle", SqlDbType.Int,4),
					new SqlParameter("@stwh_oryjstyle", SqlDbType.NVarChar,100),
					new SqlParameter("@stwh_oryjdd", SqlDbType.NVarChar,100),
					new SqlParameter("@stwh_orremark", SqlDbType.NVarChar,200),
                    new SqlParameter("@stwh_oraddress", SqlDbType.NVarChar,300),
                    new SqlParameter("@stwh_oruser", SqlDbType.NVarChar,300),
                    new SqlParameter("@stwh_ortel", SqlDbType.NVarChar,100),
                    new SqlParameter("@stwh_oryoubian", SqlDbType.NVarChar,100),
                    new SqlParameter("@stwh_orcity", SqlDbType.Int,4),
					new SqlParameter("@stwh_orid", SqlDbType.Int,4)};
			parameters[0].Value = model.stwh_buid;
			parameters[1].Value = model.stwh_orddid;
			parameters[2].Value = model.stwh_ortime;
			parameters[3].Value = model.stwh_orstatus;
			parameters[4].Value = model.stwh_orpaystyle;
			parameters[5].Value = model.stwh_oryjstyle;
			parameters[6].Value = model.stwh_oryjdd;
			parameters[7].Value = model.stwh_orremark;
            parameters[8].Value = model.stwh_oraddress;
            parameters[9].Value = model.stwh_oruser;
            parameters[10].Value = model.stwh_ortel;
            parameters[11].Value = model.stwh_oryoubian;
            parameters[12].Value = model.stwh_orcity;
            parameters[13].Value = model.stwh_orid;

			int rows=DbHelperSQL.ExecuteSql(strSql.ToString(),parameters);
            if (rows > 0) return true;
            else return false;
		}

        /// <summary>
        /// 更新订单支付状态
        /// </summary>
        /// <param name="stwh_orddid">订单编号</param>
        /// <param name="stwh_orstatus">订单状态</param>
        /// <returns></returns>
        public bool Update(string stwh_orddid, int stwh_orstatus)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update stwh_order set ");
            strSql.Append("stwh_orstatus=@stwh_orstatus");
            strSql.Append(" where stwh_orddid=@stwh_orddid");
            SqlParameter[] parameters = {
					new SqlParameter("@stwh_orstatus", SqlDbType.Int,4),
                    new SqlParameter("@stwh_orddid", SqlDbType.NVarChar,100)};
            parameters[0].Value = stwh_orstatus;
            parameters[1].Value = stwh_orddid;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0) return true;
            else return false;
        }

		/// <summary>
		/// 删除一条数据
		/// </summary>
		public bool Delete(int stwh_orid)
		{
			
			StringBuilder strSql=new StringBuilder();
			strSql.Append("delete from stwh_order ");
			strSql.Append(" where stwh_orid=@stwh_orid");
			SqlParameter[] parameters = {
					new SqlParameter("@stwh_orid", SqlDbType.Int,4)
			};
			parameters[0].Value = stwh_orid;

			int rows=DbHelperSQL.ExecuteSql(strSql.ToString(),parameters);
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}

        /// <summary>
        /// 根据订单单号删除订单
        /// </summary>
        /// <param name="stwh_orddid">订单编号</param>
        /// <returns></returns>
        public bool Delete(string stwh_orddid)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from stwh_order ");
            strSql.Append(" where stwh_orddid=@stwh_orddid;");
            strSql.Append("delete from stwh_orderdetails ");
            strSql.Append(" where stwh_orddid=@stwh_orddids;");
            SqlParameter[] parameters = {
					new SqlParameter("@stwh_orddid", SqlDbType.NVarChar,100),
                    new SqlParameter("@stwh_orddids", SqlDbType.NVarChar,100)
			};
            parameters[0].Value = stwh_orddid;
            parameters[1].Value = stwh_orddid;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0) return true;
            else return false;
        }

		/// <summary>
		/// 批量删除数据
		/// </summary>
		public bool DeleteList(string stwh_oridlist )
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("delete from stwh_order ");
			strSql.Append(" where stwh_orid in ("+stwh_oridlist + ")  ");
			int rows=DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0) return true;
            else return false;
		}

		/// <summary>
		/// 得到一个对象实体
		/// </summary>
		public stwh_Model.stwh_order GetModel(int stwh_orid)
		{
			
			StringBuilder strSql=new StringBuilder();
            strSql.Append("select  top 1 stwh_orid,stwh_buid,stwh_orddid,stwh_ortime,stwh_orstatus,stwh_orpaystyle,stwh_oryjstyle,stwh_oryjdd,stwh_orremark,stwh_bumobile,stwh_oraddress,stwh_oruser,stwh_ortel,stwh_oryoubian,stwh_orcity from view_order ");
			strSql.Append(" where stwh_orid=@stwh_orid");
			SqlParameter[] parameters = {
					new SqlParameter("@stwh_orid", SqlDbType.Int,4)
			};
			parameters[0].Value = stwh_orid;

			stwh_Model.stwh_order model=new stwh_Model.stwh_order();
			DataSet ds=DbHelperSQL.Query(strSql.ToString(),parameters);
			if(ds.Tables[0].Rows.Count>0)
			{
				return DataRowToModel(ds.Tables[0].Rows[0]);
			}
			else
			{
				return null;
			}
		}

		/// <summary>
		/// 得到一个对象实体
		/// </summary>
		public stwh_Model.stwh_order DataRowToModel(DataRow row)
		{
			stwh_Model.stwh_order model=new stwh_Model.stwh_order();
			if (row != null)
			{
				if(row["stwh_orid"]!=null)
				{
					model.stwh_orid=int.Parse(row["stwh_orid"].ToString());
				}
				if(row["stwh_buid"]!=null )
				{
					model.stwh_buid=int.Parse(row["stwh_buid"].ToString());
				}
				if(row["stwh_orddid"]!=null)
				{
					model.stwh_orddid=row["stwh_orddid"].ToString();
				}
				if(row["stwh_ortime"]!=null )
				{
					model.stwh_ortime=DateTime.Parse(row["stwh_ortime"].ToString());
				}
				if(row["stwh_orstatus"]!=null )
				{
					model.stwh_orstatus=int.Parse(row["stwh_orstatus"].ToString());
				}
				if(row["stwh_orpaystyle"]!=null )
				{
					model.stwh_orpaystyle=int.Parse(row["stwh_orpaystyle"].ToString());
				}
				if(row["stwh_oryjstyle"]!=null )
				{
					model.stwh_oryjstyle=row["stwh_oryjstyle"].ToString();
				}
                if (row["stwh_oraddress"] != null)
                {
                    model.stwh_oraddress = row["stwh_oraddress"].ToString();
                }
                if (row["stwh_oruser"] != null)
                {
                    model.stwh_oruser = row["stwh_oruser"].ToString();
                }
                if (row["stwh_ortel"] != null)
                {
                    model.stwh_ortel = row["stwh_ortel"].ToString();
                }
                if (row["stwh_oryoubian"] != null)
                {
                    model.stwh_oryoubian = row["stwh_oryoubian"].ToString();
                }
				if(row["stwh_oryjdd"]!=null)
				{
					model.stwh_oryjdd=row["stwh_oryjdd"].ToString();
				}
				if(row["stwh_orremark"]!=null)
				{
					model.stwh_orremark=row["stwh_orremark"].ToString();
				}
                if (row["stwh_orcity"] != null)
                {
                    model.stwh_orcity = int.Parse(row["stwh_orcity"].ToString());
                }
                if (row.ItemArray.Length>14)
                {
                    if (row["stwh_bumobile"] != null)
                    {
                        model.stwh_bumobile = row["stwh_bumobile"].ToString();
                    }
                }
			}
			return model;
		}

		/// <summary>
		/// 获得数据列表
		/// </summary>
		public DataSet GetList(string strWhere)
		{
			StringBuilder strSql=new StringBuilder();
            strSql.Append("select stwh_orid,stwh_buid,stwh_orddid,stwh_ortime,stwh_orstatus,stwh_orpaystyle,stwh_oryjstyle,stwh_oryjdd,stwh_orremark,stwh_bumobile,stwh_oraddress,stwh_oruser,stwh_ortel,stwh_oryoubian,stwh_orcity ");
            strSql.Append(" FROM view_order ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			return DbHelperSQL.Query(strSql.ToString());
		}

		/// <summary>
		/// 获得前几行数据
		/// </summary>
		public DataSet GetList(int Top,string strWhere,string filedOrder)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select ");
			if(Top>0)
			{
				strSql.Append(" top "+Top.ToString());
			}
            strSql.Append(" stwh_orid,stwh_buid,stwh_orddid,stwh_ortime,stwh_orstatus,stwh_orpaystyle,stwh_oryjstyle,stwh_oryjdd,stwh_orremark,stwh_bumobile,stwh_oraddress,stwh_oruser,stwh_ortel,stwh_oryoubian,stwh_orcity ");
            strSql.Append(" FROM view_order ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			strSql.Append(" order by " + filedOrder);
			return DbHelperSQL.Query(strSql.ToString());
		}

		/// <summary>
		/// 获取记录总数
		/// </summary>
		public int GetRecordCount(string strWhere)
		{
			StringBuilder strSql=new StringBuilder();
            strSql.Append("select count(1) FROM view_order ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			object obj = DbHelperSQL.GetSingle(strSql.ToString());
			if (obj == null)
			{
				return 0;
			}
			else
			{
				return Convert.ToInt32(obj);
			}
		}
		/// <summary>
		/// 分页获取数据列表
		/// </summary>
		public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("SELECT * FROM ( ");
			strSql.Append(" SELECT ROW_NUMBER() OVER (");
			if (!string.IsNullOrEmpty(orderby.Trim()))
			{
				strSql.Append("order by T." + orderby );
			}
			else
			{
				strSql.Append("order by T.stwh_orid desc");
			}
            strSql.Append(")AS Row, T.*  from view_order T ");
			if (!string.IsNullOrEmpty(strWhere.Trim()))
			{
				strSql.Append(" WHERE " + strWhere);
			}
			strSql.Append(" ) TT");
			strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
			return DbHelperSQL.Query(strSql.ToString());
		}
		#endregion  BasicMethod
	}
}

